<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">

	<title>input</title>
</head>
<body>
<?php

	//引入类库
	include "./PHPExcel-1.8/Classes/PHPExcel/IOFactory.php";


	init();

	function init() {
		echo "<a href='#' onClick='javascript:history.go(-1);'>返回上一页</a><br/>";
		$urlString =  $_GET["name"];
		openExcel("./$urlString");

	}
	function functionContentMySQL() {
		global $con;
		if(!$con ){
			$servername = "127.0.0.1";
			$username = "root";
			$password = "root";

			$con = mysql_connect($servername,$username,$password);

			if (!$con) {
			    die('Could not connect: ' . mysql_error());
			}
			mysql_select_db("hui", $con);
		} 
		$sql = "SELECT * FROM address";
		$result = mysql_query("SELECT * FROM address");

		while ($row = mysql_fetch_assoc($result)) {
		    echo $row['id'];
			echo "  ";
			echo $row['name'];
			echo "  ";  
			echo $row['phone_number'];
			echo "  ";
			echo $row['address'];    
			echo "  ";
			echo "<br/>";
		}

	}	
	function exchangeStirng($string) {
		global $con;
		if(!$con ){
			$servername = "127.0.0.1";
			$username = "root";
			$password = "root";

			$con = mysql_connect($servername,$username,$password);

			if (!$con) {
			    die('Could not connect: ' . mysql_error());
			}
			mysql_select_db("hui", $con);
		} 
		$sql = "SELECT * FROM update_table WHERE month='$string'";
		$result = mysql_query($sql);
		var_dump($result);
		if($result == FALSE) {
			return FALSE;
		} else {
			while ($row = mysql_fetch_assoc($result)) {
			    echo $row['id'];
			}

			return TRUE;
		}
		while ($row = mysql_fetch_assoc($result)) {
		    echo $row['id'];
		}
		return FALSE;
	}
	function insertExcel($string) {
		global $con;
		if(!$con ){
			$servername = "127.0.0.1";
			$username = "root";
			$password = "root";
			$con = mysql_connect($servername,$username,$password);
			if (!$con) {
			    die('Could not connect: ' . mysql_error());
			}
			mysql_select_db("hui", $con);
		} 
		$a=date("Y");
		$b=date("m");
		$c=date("d");
		$d=date("G");
		$e=date("i");
		$f=date("s");

		$addtimer = $a.$b.$c.$d.$e.$f;

		$sql = "INSERT INTO update_table (month, addtimer) VALUES ('".$string."','".$addtimer."')";
		$result = mysql_query($sql);
		if ($result) {
			// var_dump($result);
		    // echo "新记录插入成功inner<br/>";
		} else {
		    echo "Error: " . $sql . "<br>" . $conn->error;
		}
	}
	function openExcel($inputFileName){


		$sheetitemCount =0;
		date_default_timezone_set('PRC');
		// 读取excel文件
		try {
		    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
		    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
		    $objPHPExcel = $objReader->load($inputFileName);
		} catch(Exception $e) {}
		$tmpFileList =   explode('/', $inputFileName);
		$tmpFileName = array_pop($tmpFileList);
		$tmpFileName = explode('/', $tmpFileName)[0];
		// var_dump($inputFileName);
		if(exchangeStirng($tmpFileName) )  {
			echo "已经插入了";
			exit();
		} else {
			insertExcel($tmpFileName);
			// echo "$tmpFileName";
		} 
	    $sheetCount = $objPHPExcel->getSheetCount();//获取sheet工作表总个数

	    $sheetCount =1;
		$jumpForFunction = FALSE;
		$nextPayMoney = FALSE;
		$nextRow = FALSE;	
		$nextRowIndex = -1;
		$shopNameIndex = -1;
		$shopUnitIndex = -1;
		$shopAddressIndex = -1;
		$shopspecificationsIndex = -1;
		$shopNumberIndex = -1;
		$shopPriceIndex = -1;
		$shopOrderMoneyIndex = -1;
		$shopOtherIndex = -1;
		$dataOneRow = [];
		$dataOneOrder = [];
		$dataRows = [];
		$dataOneOrder["list"]  = [];
	    for ($sheetCountNumber=0; $sheetCountNumber < $sheetCount; $sheetCountNumber++) { 
	        $sheet = $objPHPExcel->getSheet($sheetCountNumber);
	        $highestRow = $sheet->getHighestRow();
	        $highestColumn = $sheet->getHighestColumn();
	            // echo "$highestRow<br/>";

	        // 获取excel文件的数据，$row=2代表从第二行开始获取数据
	        for ($row = 1; $row <= $highestRow; $row++){
	            $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
	            for ($item=0; $item < count($rowData); $item++) { 
	      			$dataOneRow = [];
					$dataOneRow["name"] = "";
					$dataOneRow["unit"] = "";
					$dataOneRow["address"] = "";
					$dataOneRow["specifications"] = "";
					$dataOneRow["number"] = "";
					$dataOneRow["price"] = "";
					$dataOneRow["orderMoney"] = "";
					$dataOneRow["other"] = "";

	            	for ($item2=0; $item2 < count($rowData[$item]); $item2++) { 
	            		$itemString =  $rowData[$item][$item2];

						if($itemString == "江苏泰州鹏程动物药业销售单") {
							$sheetitemCount++;
							// echo "$sheetitemCount";
						}
	            		if($itemString !=NULL) {
	            			// if($sheetitemCount == 24){
	            			// 	echo "<br/>";
	            			//  	echo "$itemString  ";
	            			// }

	 
	            			if(strpos($itemString, "录单日期：") === 0){
								date_default_timezone_set ("Asia/Chongqing");
								$a=date("Y");
								$b=date("m");
								$c=date("d");
								$d=date("G");
								$e=date("i");
								$f=date("s");

								$dataOneOrder = [];
								$dataOneOrder["list"]  = [];
	            				$dataOneOrder["order_id"] = $a.$b.$c.$d.$e.$f.( rand(1000000, 9999999)%9000 + 1000).( rand(1000000, 9999999)%9000 + 1000).( rand(1000000, 9999999)%9000 + 1000);
	            				$dataOneOrder["add_date"] = substr($itemString, strlen( "录单日期："));
	            			} else if(strpos($itemString, "录单日期:") === 0){
								date_default_timezone_set ("Asia/Chongqing");
								$a=date("Y");
								$b=date("m");
								$c=date("d");
								$d=date("G");
								$e=date("i");
								$f=date("s");

								$dataOneOrder = [];
								$dataOneOrder["list"]  = [];
	            				$dataOneOrder["order_id"] = $a.$b.$c.$d.$e.$f.(time()%9000 + 1000);
	            				$dataOneOrder["add_date"] = substr($itemString, strlen( "录单日期:"));
	            			}
							if(strpos($itemString, "经手人：") === 0){
	            				$dataOneOrder["salesman_name"]=substr($itemString, strlen( "经手人："));
	            			} else 
							if(strpos($itemString, "经手人:") === 0){
	            				$dataOneOrder["salesman_name"]=substr($itemString, strlen( "经手人:"));
	            			}
							
							if(strpos($itemString, "发货仓库：") === 0){
	            				$dataOneOrder["shopping_address"]=substr($itemString, strlen( "发货仓库："));
	            			} else 
							if(strpos($itemString, "发货仓库:") === 0){
	            				$dataOneOrder["shopping_address"]=substr($itemString, strlen( "发货仓库:"));
	            			}
							
							if(strpos($itemString, "购买单位：") === 0){
	            				$dataOneOrder["address_name"]=substr($itemString, strlen( "购买单位："));
	            			} else 

							if(strpos($itemString, "购买单位:") === 0){
	            				$dataOneOrder["address_name"]=substr($itemString, strlen( "购买单位:"));
	            			}


	            			if($itemString === "商品全名"){
								$shopNameIndex = $item2;
	            			}
							if($itemString === "单位"){
								$shopUnitIndex = $item2;
	            			}

							if($itemString === "产地"){
								$shopAddressIndex = $item2;
	            			}

							if($itemString === "规格"){
								$shopspecificationsIndex = $item2;
	            			}

							if($itemString === "数量"){
								$shopNumberIndex = $item2;
	            			}

							if($itemString === "单价"){
								$shopPriceIndex = $item2;
	            			}

							if($itemString === "金额（元）"){
								$shopOrderMoneyIndex = $item2;
	            			}


	            			if($itemString === "备注"){
	            				$shopOtherIndex = $item2;

								$nextRow = TRUE;
								$nextRowIndex = -1;
	            			} else if($nextRow){
	            			 	// echo "$itemString  ";

								if($shopNameIndex == $item2){
									// $shopNameIndex = $item2;商品全名
									$dataOneRow["name"] = $itemString;
		            			}

								if($shopUnitIndex == $item2){
									// $shopUnitIndex = $item2;
									// $itemString === "单位"
										$dataOneRow["unit"] = $itemString;
		            			}

								if($shopAddressIndex == $item2){
									// $shopAddressIndex = $item2;$itemString === "产地"
		            				$dataOneRow["address"] = $itemString;
		            			}

								if($shopspecificationsIndex == $item2){
									// $shopspecificationsIndex = $item2;$itemString === "规格"
		            				$dataOneRow["specifications"] = $itemString;
		            			}

								if($shopNumberIndex == $item2){
									// ;$itemString === "数量"
		            				$dataOneRow["number"] = $itemString;
		            			}

								if($shopPriceIndex == $item2){
									// $itemString === "单价";
		            				$dataOneRow["price"] = $itemString;

								}

								if($shopOrderMoneyIndex == $item2){
									// ;$itemString === "金额（元）"
									$dataOneRow["orderMoney"] = $itemString;

		            			}
		            			if($shopOtherIndex == $item2){

									$dataOneRow["other"] = $itemString;

		            			}
							}


							
	            			if($itemString === "合计"){
								$nextPayMoney = TRUE;
								$nextRow = FALSE;
								$nextRowIndex = -1;
								$dataRows = [];

	            			} else
	            			if($nextPayMoney && $itemString > 0){
								$nextPayMoney = FALSE;
								$dataOneOrder["order_price"]=$itemString;

	            			}

							if(strpos($itemString, "收款账户：") === 0){
	            				$dataOneOrder["pay_type"]=substr($itemString, strlen( "收款账户："));
	            			}	 else if(strpos($itemString, "收款账户:") === 0){
	            				$dataOneOrder["pay_type"]=substr($itemString, strlen( "收款账户:"));
	            			}
	            			if(strpos($itemString, "收款金额：") === 0){
	            				$dataOneOrder["order_pay_price"]=substr($itemString, strlen( "收款金额："));
	            			} else 	if(strpos($itemString, "收款金额:") === 0){
	            				$dataOneOrder["order_pay_price"]=substr($itemString, strlen( "收款金额:"));
	            			}	
	            			// echo $item2;
	            			// echo " ";
	            			// echo $row;

	            			if(strpos($itemString, "合计金额：") === 0 ){
								$dataOneOrder["order_pay_price2"]=substr($itemString, strlen( "合计金额："));	
								$nextPayMoney = FALSE; 
								$nextRow = FALSE;
      
								$shopNameIndex = -1;
								$shopUnitIndex = -1;
								$shopAddressIndex = -1;
								$shopspecificationsIndex = -1;
								$shopNumberIndex = -1;
								$shopPriceIndex = -1;
								$shopOrderMoneyIndex = -1;
								$shopOtherIndex = -1;

								// if($row > 30)
								{
									// $jumpForFunction = TRUE;
										insertRow($dataOneOrder);
								}
   	            			} else 
							if(strpos($itemString, "合计金额:") === 0 ){
								$dataOneOrder["order_pay_price2"]=substr($itemString, strlen( "合计金额:"));	
								$nextPayMoney = FALSE; 
								$nextRow = FALSE;
      
								$shopNameIndex = -1;
								$shopUnitIndex = -1;
								$shopAddressIndex = -1;
								$shopspecificationsIndex = -1;
								$shopNumberIndex = -1;
								$shopPriceIndex = -1;
								$shopOrderMoneyIndex = -1;
								$shopOtherIndex = -1;

								// if($row > 30)
								{
									// $jumpForFunction = TRUE;
										insertRow($dataOneOrder);
								}
   	            			}

	            			if($jumpForFunction) break;
	            		}
	            	}
					if($nextRow){
						if(count($dataOneRow) > 0 && $nextRowIndex >= 0 ) {
							$dataRows[$nextRowIndex] =  $dataOneRow;
	            			$dataOneOrder["list"] =  $dataRows;		
	            			$dataOneRow = [];
	            		}
						$nextRowIndex++;
					}
	            	if($jumpForFunction) break;
	            }
			
	            if($jumpForFunction) break;
	        }
	    }
	    global $indexValue;
	    echo "<br/>一共添加： $indexValue 条";
	}
	$indexValue = 0;
	function insertRow($dataOneOrder){
		    global $indexValue;
		   $indexValue +=1;
		// echo $indexValue;
		// echo "<br/>";
		// if($indexValue == 24)
		//  {
		// 	var_dump($dataOneOrder);
		// 	//

		// }
		 insertIntoMain($dataOneOrder);
			
	}	
	$con;
	
	function insertIntoMain($dataOneOrder){
		$listInner = $dataOneOrder['list'];
		// echo "******".count( $listInner). "*****<br/>" ;
		if(count( $listInner) == 0) echo "******".count( $listInner). "*****<br/>" ;
		$listInnerAllMoney = $dataOneOrder['order_pay_price2'];
		$tmpAllMoney = 0;
		for ($i=0; $i < count($listInner); $i++) { 
			$i1 = $i+1;
			$innerItem = $listInner[$i];
			if($innerItem["name"] && strlen($innerItem["name"]) > 0){
				$tmpAllMoney += $innerItem['orderMoney'];
				insertIntoInner($innerItem,$dataOneOrder['order_id'], $dataOneOrder["address_name"], $dataOneOrder['add_date'], $dataOneOrder['shopping_address'],$dataOneOrder['order_id']."-".$i1);
			}
		}
		if($listInnerAllMoney != $tmpAllMoney && $tmpAllMoney > 0) {
			 $dataOneOrder['order_pay_price2'] =  $tmpAllMoney;
		}
  		global $con;
		if($con ==NULL || !$con ){
			$servername = "127.0.0.1";
			$username = "root";
			$password = "root";

			$con = mysql_connect($servername,$username,$password);

			if (!$con) {
			    die('Could not connect: ' . mysql_error());
			}
			mysql_select_db("hui", $con);
		} 
		$sql = "INSERT INTO order_main (order_id, add_date, salesman_name, shopping_address, address_name, pay_type, order_pay_price, order_pay_price2) VALUES ('".$dataOneOrder["order_id"]."','".$dataOneOrder["add_date"]."','".$dataOneOrder["salesman_name"]."','".$dataOneOrder["shopping_address"]."','".$dataOneOrder["address_name"]."','".$dataOneOrder["pay_type"]."','".$dataOneOrder["order_pay_price"]."','".$dataOneOrder["order_pay_price2"]."')";
		$result = mysql_query($sql);
		if ($result) {
			// var_dump($result);
		    // echo "新记录插入成功main<br/>";
		} else {
		    echo "Error: " . $sql . "<br>" . $conn->error;
		}
		 
	}
	function insertIntoInner($inner_item,$order_main_id, $user_name, $add_time, $delivery_warehouse, $inner_id){
  		global $con;
		if($con ==NULL || !$con ){
			$servername = "127.0.0.1";
			$username = "root";
			$password = "root";

			$con = mysql_connect($servername,$username,$password);

			if (!$con) {
			    die('Could not connect: ' . mysql_error());
			}
			mysql_select_db("hui", $con);
		} 
		$sql = "INSERT INTO order_inner (order_main_id, order_inner_id, user_name, add_time, goods_unit,goods_name,goods_address,buy_number, specifications, shop_price, sale_price, info, delivery_warehouse) VALUES ('".$order_main_id."','".$inner_id."','".$user_name."','".$add_time."','".$inner_item['unit']."','".$inner_item['name']."','".$inner_item['address']."','".$inner_item['number']."','".$inner_item['specifications']."','".$inner_item['price']."','".$inner_item['orderMoney']."','".$inner_item['other']."','".$delivery_warehouse."')";
		$result = mysql_query($sql);
		if ($result) {
			// var_dump($result);
		    // echo "新记录插入成功inner<br/>";
		} else {
		    echo "Error: " . $sql . "<br>" . $conn->error;
		}
		 
	}

	function openExcelError(){
		$sheet = $objPHPExcel->getSheet(0);
		$highestRow = $sheet->getHighestRow();
		$highestColumn = $sheet->getHighestColumn();

		// 获取excel文件的数据，$row=2代表从第二行开始获取数据
		for ($row = 1; $row <= $highestRow; $row++){
		// Read a row of data into an array
		    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
		//这里得到的rowData都是一行的数据，得到数据后自行处理，我们这里只打出来看看效果
		    echo '<pre>';
		    var_dump($rowData);
		    var_dump("--------------------------");
		    echo "<br>";

		}
	}
	function openExcel1($inputFileName){
		// //elsx文件路径
		// $inputFileName = "./2019.xls";
		date_default_timezone_set('PRC');
		// 读取excel文件
		try {
		    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
		    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
		    $objPHPExcel = $objReader->load($inputFileName);
		} catch(Exception $e) {}

	    $sheetCount = $objPHPExcel->getSheetCount();//获取sheet工作表总个数

	    $sheetCount =1;

	    for ($sheetCountNumber=0; $sheetCountNumber < $sheetCount; $sheetCountNumber++) { 
	        $sheet = $objPHPExcel->getSheet($sheetCountNumber);
	        $highestRow = $sheet->getHighestRow();
	        $highestColumn = $sheet->getHighestColumn();

	        // 获取excel文件的数据，$row=2代表从第二行开始获取数据
	        for ($row = 1; $row <= $highestRow; $row++){
	            $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, "000", TRUE, FALSE);
	        //这里得到的rowData都是一行的数据，得到数据后自行处理，我们这里只打出来看看效果
	            echo '<pre>';
	            echo "$row: <br/><br/>";
	            for ($item=0; $item < count($rowData); $item++) { 
	      
	            	for ($item2=0; $item2 < count($rowData[$item]); $item2++) { 
	            		$itemString =  $rowData[$item][$item2];
	            		if($itemString !=NULL) 
	            		echo "$itemString    ";	           	 	
	            	}
	            }

	            // var_dump($rowData);
	            echo "<br><br>";

	        }
	    }
	    exit();
	}
?>


</html>